/*==============================================================*/
/* Table: OWNER                                                 */
/*==============================================================*/
create table OWNER (
   OWNER_FEDERAL_TAX_id_NUMBER int                  not null,
   OWNER_NAME  VarChar (25)         not null,   --__AK
   OWNER_ADDRESS   VarChar (35)         not null, 
   OWNER_CITY      VarChar (12)         not null, 
   OWNER_STATE VarChar (12)         not null,  --__AK
   OWNER_COUNTRY   VarChar (12)         not null,
   OWNER_ZIP_CODE  int                  not null,
   OWNDER_CONTACT_PERSON VarChar (25)         not null,
   OWNER_CONTACT_PHONE_NUMBER_1 int                  not null,
   OWNER_CONTACT_PHONE_NUMBER_2 int                  null,
   OWNER_FAX_NUMBER_1   int                  null,
   OWNER_FAX_NUMBER_2   int                  null,
   OWNER_E_MAIL_ADDRESS VarChar (20)         not null,
   OWNER_WEBSITE        VarChar (30)         null,
   OWNER_GENERAL_INFORMATION VarChar (100)        null,
   constraint PK_OWNER primary key  (OWNER_FEDERAL_TAX_ID_NUMBER)
)

/*==============================================================*/
/* Table: PROJECT                                               */
/*==============================================================*/
create table PROJECT (
   PROJECT_NAME     VarChar (25)        not null,  
   TYPE_OF_PROJECT_1  VarChar (30)        not null,
   TYPE_OF_PROJECT_2  VarChar (30)        not null,
   PROJECT_DESCRIPTION   VarChar (100)       null,
   PROJECT_LOCATION_ADDRESS  VarChar (25)        not null,
   PROJECT_CITY          VarChar (15)        not null,
   PROJECT_STATE         VarChar (15)        not null,
   PROJECT_ZIP_CODE int                  not null,
   PROJECT_COUNTRY  VarChar (15)        not null,
   GENERAL_CONTRACTOR  VarChar (35)        not null,   __AK_
   PROJECT_MANAGER_NAME  VarChar (25)        not null,
   PROJECT_MANAGER_S_COMPANY  VarChar (35)        not null,
   PROJECT_MANAGER_TELEPHONE_NUMBER int                  not null,
   PROJECT_MANAGER_E_MAIL_ADDRESS  VarChar (20)        null,
   DELIVERY_METHOD  VarChar (25)        not null,
   CONTRACT_TYPE    VarChar (30)        not null,
   TOTAL_BUDGET_EXPECTED_COST int                  null,
   PROJECT_PHONE_NUMBER_1 int                  not null,
   PROJECT_PHONE_NUMBER_2 int                  null,
   PROJECT_FAX_NUMBER_1 int                  null,
   PROJECT_FAX_NUMBER_2 int                  null,
   OWNER_FEDERAL_TAX_ID_NUMBER int                  not null,   __FK_
   constraint PK_PROJECT primary key  (PROJECT_NAME, OWNER_FEDERAL_TAX_ID_NUMBER)
)
add Constraint fk_project foreign key (owner_federal_tax_id_number)
references owner(owner_federal_tax_id_number)
on update cascade
on delete cascade
go

/*==============================================================*/
/* Table: CONTRACTOR                                            */
/*==============================================================*/
create table CONTRACTOR (
   CONTRACTOR_FEDERAL_TAX_IDENTIFICATION_NUMBER int                  not null,  --__AK_
   CONTRACTOR_REGISTERED_NAME VarChar (25)         not null,
   CONTRACTOR_ADDRESS VarChar (35)         not null,
   CONTRACTOR_CITY VarChar (12)         not null,
   CONTRACTOR_STATE     VarChar (12)         not null,
   CONTRACTOR_COUNTRY VarChar (12)         not null,
   CONTRACTOR_ZIP_CODE int                  not null,
   CONTRACTOR_CONTACT_PERSON_ON_SITE VarChar (25)         not null,
   RESPONSIBILITY_OF_CONTACT_PERSON VarChar (25)         null,
   CONTRACTOR_CONTACT_PHONE_NUMBER_1 int                  not null,
   CONTRACTOR_CONTACT_PHONE_NUMBER_2 int                  null,
   CONTRACTOR_FAX_NUMBER_1 int                  null,
   CONTRACTOR_FAX_NUMBER_2 int                  null,
   CONTRACTOR_CONTACT_E_MAIL_ADDRESS VarChar (20)         null,
   CONTRACTOR_WEBSITE   VarChar (30)         null,
   CONTRACTOR_GENERAL_INFORMATION VarChar (100)        null,
   constraint PK_CONTRACTOR primary key  (CONTRACTOR_REGISTERED_NAME, CONTRACTOR_STATE)
)
go

/*==============================================================*/
/* Table: PROJECT_CONTRACTOR_CMB                                */
/*==============================================================*/
create table PROJECT_CONTRACTOR_CMB (
   PROJECT_NAME VarChar (25)         not null,  --__FK__AK_
   CONTRACTOR_REGISTERED_NAME VarChar (25)         not null,  --__FK__AK_
   CONTRACTOR_STATE VarChar (12)         not null,  --__FK__AK_
   PROJECT_CONTRACTOR_SURROGATE_KEY int                  not null,
   constraint PK_PROJECT_CONTRACTOR_CMB primary key  (PROJECT_CONTRACTOR_SURROGATE_KEY)
)
add Constraint fk_contractor foreign key (contractor_registered_name, contractor_state)
references contractor(contractor_registered_name, contractor_state)
on update cascade
on delete cascade

add Constraint fk_project foreign key (project_name)
references project(project_name)
on update cascade
on delete cascade
go

/*==============================================================*/
/* Table: ACTIVITY                                              */
/*==============================================================*/
create table ACTIVITY (
   ACTIVITY_ID          int                  not null,
   ACTIVITY_DESCRIPTION VarChar (100)        not null,
   
   ACTIVITY_S_EARLY_START_DATE datetime             not null,
   ACTIVITY_S_EARLY_FINISH_DATE datetime             not null,
   ACTIVITY_S_LATE_START_DATE datetime             null,
   ACTIVITY_S_LATE_FINISH_DATE datetime             null,
   ACTIVITY_FLOW        int                  null,
   PREDECESSOR_ACTIVITIES int                  not null,
   SUCCESSOR_ACTIVITIES int                  not null,
   PROJECT_CONTRACTOR_S_SURROGATE_KEY int                  not null,  --__FK_
   ACTIVITY_DURATION_IN_DAYS int                  not null,
   ACTIVITY_DEADLINE    datetime             null,
   ACTUAL_DURATION      int                  null,
   ACTUAL_FINISH        datetime             null,
   ACTUAL_START         datetime             null,
   CRITICAL_ACTIVITY    varchar(1)           null,
   FLAG                 varchar(1)           null,
   MILESTONE            varchar(1)           null,
   PRIORITY             int                  null,
   STATUS_INDICATOR     varchar(20)          null,
   CALENDAR             int                  null,
   CONSTRAINT_TYPE      varchar(30)          null,
   CONSTRAINT_DATE      datetime             null,
   WBS_CODE             int                  null,
   EARNED_EVALUATION_METHOD int                  null,
   PERCENT_COMPLETE     int                  null,
   PHYSICAL_PERCENT_COMPLETE int                  null,
   CONTACT              varchar(30)          null,
   BASELINE_COST        int                  null,
   ACTUAL_COST          int                  null,
   ACTIVITY_SITUATION   varchar(30)          not null,
   constraint PK_ACTIVITY primary key  (ACTIVITY_ID )
)
add Constraint fk_project_contractor_cmb foreign key (project_contractor_surrogate_key)
references project_contractor_surrogate_key(project_contractor_surrogate_key)
on update cascade
on delete cascade
go

/*==============================================================*/
/* Table: ACTIVITY_MATERIAL_CMB                                 */
/*==============================================================*/
create table ACTIVITY_MATERIAL_CMB (
   ACTIVITY_ID          int                  not null,
   MATERIAL_COMPONENT_SURROGATE_KEY int                  not null  --__pK_ int
constraint PK_ACTIVITY primary key  (ACTIVITY_ID, MATERIAL_COMPONENT_SURROGATE_KEY )
)
add Constraint fk_activity foreign key (activity_id)
references activity(activity_id)
on update cascade
on delete cascade

add constraint fk_material_components(material_component_surrogate_key)
references material_components(material_component_surrogate_key)
on update cascade
on delete cascade
go

/*==============================================================*/
/* Table: MATERIAL_COMPONENTS                                   */
/*==============================================================*/
create table MATERIAL_COMPONENTS (
   MATERIALS_COMMODITY_CODE_ID int                  not null,  --__AK_
   MATERIAL_PROPERTIES  VarChar (100)        null,
   MATERIAL_SPECIFICATIONS VarChar (100)        not null,
   INSTALLED_STATUS     varchar(1)           null,
   INSTALLED_NOTES      VarChar (100)        null,
   INSTALLED_LOCATION   varchar(15)              null,  --(x,y,z) syntax via x_y_z
   ACCEPTANCE_STATUS    VarChar (14)         null,
   ITEM_NEED_DATE       datetime             null,
   MATERIAL_LEAD_TIME   int                  null,
   EXPECTED_DELIVERY_DATE datetime             null,
   PURCHASE_ORDER_NUMBER int                  null,
   INVOICE_NUMBER int                  null,  --__FK__AK_
   PURCHASER_FEDERAL_TAX_IDENTIFICATION_NUMBER int                  null,
   SUPPLIER_FEDERAL_TAX_IDENTIFICATION_NUMBER int                  null,
   SUPPLIER_FEDERAL_TAX_IDENTIFICATION_NUMBER int                  null,  --__FK__AK_
   MATERIAL_COMPONENT_SURROGATE_KEY int                  not null,  --__PK_
   MATERIALS_NAME       varchar(40)          not null,
   INITIALS             varchar(10)          null,
   SINGLE_SOURCE        varchar(1)           null,
   PROCUREMENT_STATUS   varchar(16)          null,
   MATERIAL_LABEL_Or_UNITS varchar(5)           null,
   STANDARD_RATE        money                null,
   COST_PER_USE         money                null,
   MATERIAL_GROUP       varchar(10)          null,
   ACCRUED_AT           varchar(8)           null,
   BASELINE_COST        money                null,
   ACTUAL_COST          money                null,
   FLAG                 varchar(1)           null,
   constraint PK_MATERIAL_COMPONENTS primary key  (MATERIAL_COMPONENT_SURROGATE_KEY)
)
add Constraint fk_invoice foreign key (invoice_number, supplier_federal_tax_identification_number)
references invoice(invoice_number, supplier_federal_tax_identification_number)
on update cascade
on delete cascade
go

/*==============================================================*/
/* Table: INVOICE                                               */
/*==============================================================*/
create table INVOICE (
   INVOICE_NUMBER       int                  not null,
   INVOICE_DATE    datetime             not null,
   PAYMENT_TERMS   Char(15)             not null,
   PURCHASE_ORDER_NUMBER int                  null,  --__FK_
   PURCHASER_FEDERAL_TAX_IDENTIFICATION_NUMBER int                  null,  --__FK_
   SUPPLIER_FEDERAL_TAX_IDENTIFICATION_NUMBER int                  not null,  --__FK_
   MATERIALS_COMMODITY_CODES_ID int        not null,     -- * n times
   SUPPLIED_MATERIAL_PROPERTIES VarChar (100)    null,        -- * n
   SUPPLIED_MATERIAL_SPECIFICATIONS VarChar (100)    not null,     -- * n
   TOTAL_UNIT_COST int              not null,  -- * n
   TOTAL_INVOICE_COST int                  not null,
   constraint PK_INVOICE primary key  (INVOICE_NUMBER, SUPPLIER_FEDERAL_TAX_IDENTIFICATION_NUMBER)
)
add Constraint fk_material_components foreign key (purchase_order_number, purchaser_federal_tax_identification_number, supplier_federal_tax_identification_number)
references material_components(purchase_order_number, purchaser_federal_tax_identification_number, supplier_federal_tax_identification_number)
on update cascade
on delete cascade
go

/*==============================================================*/
/* Table: MATERIAL_SUPPLIER                                     */
/*==============================================================*/
create table MATERIAL_SUPPLIER (
   SUPPLIER_FEDERAL_TAX_IDENTIFICATION_NUMBER int                  not null,
   SUPPLIER_REGISTERED_NAME VarChar (25)         not null,  --__AK_
   SUPPLIER_ADDRESS VarChar (35)         not null,
   SUPPLIER_CITY   VarChar (12)         not null,
   SUPPLIER_STATE  VarChar (12)         not null,  --__ak_
   SUPPLIER_COUNTRY VarChar (12)         not null,
   SUPPLIER_ZIP_CODE int                  not null,
   SUPPLIER_CONTACT_PERSON VarChar (25)         not null,
   RESPONSIBILITY_OF_CONTACT_PERSON VarChar (25)         null,
   SUPPLIER_CONTACT_PHONE_NUMBER_1 int                  not null,
   SUPPLIER_CONTACT_NUMBER_2 int                  null,
   SUPPLIER_FAX_NUMBER_1 int                  null,
   SUPPLIER_FAX_NUMBER_2 int                  null,
   SUPPLIER_CONTACT_E_MAIL_ADDRESS VarChar (20)         not null,
   SUPPLIER_WEBSITE     VarChar (30)         null,
   SUPPLIER_GENERAL_INFORMATION VarChar (100)        null,
   constraint PK_MATERIAL_SUPPLIER primary key  (SUPPLIER_FEDERAL_TAX_IDENTIFICATION_NUMBER)
)
go

/*==============================================================*/
/* Table: CURRENT_POSITION                                      */
/*==============================================================*/
create table CURRENT_POSITION (
   LOCATION__NN_        varchar(15)              not null, --(x,y,z) syntax via x_y_z
   TIME_AND_DATE        datetime             not null,
   MATERIAL_COMPONENT_SURROGATE_KEY int                  not null,  --__PK_
   MATERIAL_SITUATION   varchar(30)          not null,
   constraint PK_CURRENT_POSITION primary key  (TIME_AND_DATE, MATERIAL_COMPONENT_SURROGATE_KEY)
)
go

/*==============================================================*/
/* Table: PAST_POSITIONS                                        */
/*==============================================================*/
create table PAST_POSITIONS (
   LOCATION___NN_       varchar(15)              not null, --(x,y,z) syntax via x_y_z
   TIME_AND_DATE        datetime             not null,
   MATERIAL_COMPONENT_SURROGATE_KEY int                  not null,  --__PK_
   MATERIAL_SITUATION   varchar(30)          not null,
   constraint PK_PAST_POSITIONS primary key  (TIME_AND_DATE, MATERIAL_COMPONENT_SURROGATE_KEY)
)
go